package com.gitee.loyo.jdbc

import com.gitee.loyo.*
import com.gitee.loyo.interfaces.ParameterizeSQL
import com.gitee.loyo.interfaces.TypeReference
import java.lang.UnsupportedOperationException
import java.lang.reflect.Method
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.Statement
import kotlin.reflect.KClass

class Conn(private val conn: Connection, hold: Boolean = true): Connection by conn {
    var executeOnce = false

    init {
        if(hold)
            connThreadLocal.set(this)
    }

    inline fun <reified T: Any> queryList(parameterizeSQL: ParameterizeSQL): List<T>{
        return query(parameterizeSQL).asList()
    }

    inline fun <reified T: Any> queryList(opt: StmtOptions = StmtOptions(), parameterizeSQL: ParameterizeSQL): List<T>{
        return query(opt, parameterizeSQL).asList()
    }

    inline fun <reified T: Any> queryOne( parameterizeSQL: ParameterizeSQL): T?{
        return query(parameterizeSQL).`as`()
    }

    inline fun <reified T: Any> queryOne(opt: StmtOptions = StmtOptions(), parameterizeSQL: ParameterizeSQL): T?{
        return query(opt, parameterizeSQL).`as`()
    }

    fun query(parameterizeSQL: ParameterizeSQL): Rs{
        return query(StmtOptions(), parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun query(opt: StmtOptions = StmtOptions(), parameterizeSQL: ParameterizeSQL): Rs{
        return query(opt, parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    inline fun <reified T: Any> queryList(sql: String, vararg args: Any?): List<T>{
        return query(sql, *args).asList()
    }

    inline fun <reified T: Any> queryList(opt: StmtOptions = StmtOptions(), sql: String, vararg args: Any?): List<T>{
        return query(opt, sql, *args).asList()
    }

    inline fun <reified T: Any> queryOne(sql: String, vararg args: Any?): T?{
        return query(sql, *args).`as`()
    }

    inline fun <reified T: Any> queryOne(opt: StmtOptions = StmtOptions(), sql: String, vararg args: Any?): T?{
        return query(opt, sql, *args).`as`()
    }

    fun query(sql: String, vararg args: Any?): Rs{
        return query(StmtOptions(), sql, *args)
    }

    fun query(opt: StmtOptions = StmtOptions(), sql: String, vararg args: Any?): Rs{
        prepareStatement(opt, sql).also { stmt ->
            stmt.handleParameters(this, *args)
            return Rs(stmt.executeQuery()).also {
                JDBCListener.onExecutedListener?.invoke(this, stmt, com.gitee.loyo.ExecuteType.Query)
            }.setCloseListener {
                stmt.close()
            }
        }
    }

    inline fun <reified T: Any> queryPage(parameterizeSQL: ParameterizeSQL): PageQuery<T>{
        return queryPage(StmtOptions(), parameterizeSQL)
    }

    inline fun <reified T: Any> queryPage(opt: StmtOptions = StmtOptions(), parameterizeSQL: ParameterizeSQL): PageQuery<T>{
        return queryPage(opt, T::class, parameterizeSQL)
    }

    inline fun <reified T: Any> queryPage(sql: String, vararg args: Any?): PageQuery<T>{
        return queryPage(StmtOptions(), sql, *args)
    }

    inline fun <reified T: Any> queryPage(opt: StmtOptions = StmtOptions(), sql: String, vararg args: Any?): PageQuery<T>{
        return queryPage(opt, T::class, sql, *args)
    }

    fun <T: Any> queryPage(opt: StmtOptions, type: KClass<T>, parameterizeSQL: ParameterizeSQL): PageQuery<T>{
        return queryPage(opt, type, parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun <T: Any> queryPage(opt: StmtOptions, type: KClass<T>, sql: String, vararg args: Any?): PageQuery<T>{
        return PageQuery.query(this, type, opt, sql, *args)
    }

    fun <T: Any> queryPage(opt: StmtOptions, type: KClass<T>, parameterizeCountSQL: ParameterizeSQL, parameterizePageSQL: ParameterizeSQL): Pair<Int, List<T>>{
        val count = query(opt, parameterizeCountSQL).asInt()
        return if(count == 0){
            count to emptyList()
        }else{
            count to query(opt, parameterizePageSQL).asList(type)
        }
    }

    fun insertForGeneratedKey(parameterizeSQL: ParameterizeSQL): Any?{
        return insertForGeneratedKey(StmtOptions(), parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun insertForGeneratedKey(opt: StmtOptions = StmtOptions(), parameterizeSQL: ParameterizeSQL): Any?{
        return insertForGeneratedKey(opt, parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun insertForGeneratedKey(sql: String, vararg args: Any?): Any?{
        return insertForGeneratedKey(StmtOptions(), sql, *args)
    }

    fun insertForGeneratedKey(opt: StmtOptions = StmtOptions(), sql: String, vararg args: Any?): Any?{
        prepareStatement(opt, sql, Statement.RETURN_GENERATED_KEYS).use { stmt ->
            stmt.handleParameters(this, *args)
            return if(stmt.executeUpdate() > 0){
                Rs(stmt.generatedKeys).getGeneratedKey().also {
                    JDBCListener.onExecutedListener?.invoke(this, stmt, com.gitee.loyo.ExecuteType.Update)
                }
            }else{
                null
            }
        }
    }

    fun update(parameterizeSQL: ParameterizeSQL): Int{
        return update(StmtOptions(), parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun update(opt: StmtOptions = StmtOptions(), parameterizeSQL: ParameterizeSQL): Int{
        return update(opt, parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun update(sql: String, vararg args: Any?): Int{
        return update(StmtOptions(), sql, *args)
    }
    fun update(opt: StmtOptions = StmtOptions(), sql: String, vararg args: Any?): Int{
        prepareStatement(opt, sql).use { stmt ->
            stmt.handleParameters(this, *args)
            return stmt.executeUpdate().also {
                JDBCListener.onExecutedListener?.invoke(this, stmt, com.gitee.loyo.ExecuteType.Update)
            }
        }
    }

    fun largeUpdate( parameterizeSQL: ParameterizeSQL): Long{
        return largeUpdate(parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun largeUpdate(opt: StmtOptions = StmtOptions(), parameterizeSQL: ParameterizeSQL): Long{
        return largeUpdate(opt, parameterizeSQL.getSQL(), *parameterizeSQL.getParameters())
    }

    fun largeUpdate(sql: String, vararg args: Any?): Long{
        return largeUpdate(StmtOptions(), sql, *args)
    }

    fun largeUpdate(opt: StmtOptions = StmtOptions(), sql: String, vararg args: Any?): Long{
        prepareStatement(opt, sql).use { stmt ->
            stmt.handleParameters(this, *args)
            return stmt.executeLargeUpdate().also {
                JDBCListener.onExecutedListener?.invoke(this, stmt, com.gitee.loyo.ExecuteType.Update)
            }
        }
    }

    fun batch(sql: String, argsList: List<List<Any?>>): IntArray{
        return batch(StmtOptions(), sql, argsList)
    }

    fun batch(opt: StmtOptions = StmtOptions(), sql: String, argsList: List<List<Any?>>): IntArray{
        prepareStatement(opt, sql).use { stmt ->
            argsList.forEach { args ->
                stmt.handleParameters(this, *(args.toTypedArray()))
                stmt.addBatch()
            }
            return stmt.executeBatch().also {
                JDBCListener.onExecutedListener?.invoke(this, stmt, com.gitee.loyo.ExecuteType.Update)
            }
        }
    }


    inline fun <reified T: Any> mapper(): T{
        return Helper.mapper()
    }

     fun invokeMapperFunction(method: Method, argsMap: Map<String, Any?>): Any? {
        val resultKType = method.returnType.kotlin

        val query = method.getAnnotation(Query::class.java)
        if(query != null){
            return when (resultKType) {
                Rs::class -> {
                    query(KSQL.templateHandler.template(query.value, argsMap))
                }
                List::class -> {
                    val type = KSQL.reflection.getGenericReturnTypeFromMethod(method)
                    query(KSQL.templateHandler.template(query.value, argsMap)).asList(object : TypeReference<Any>(type!!){})
                }
                Pair::class, Triple::class -> {
                    query(KSQL.templateHandler.template(query.value, argsMap)).`as`(object : TypeReference<Any>(method.genericReturnType){})
                }
                else -> query(KSQL.templateHandler.template(query.value, argsMap)).`as`(resultKType)
            }
        }

        val update = method.getAnnotation(Update::class.java)
        if(update != null){
            return when(resultKType){
                Int::class -> update(KSQL.templateHandler.template(update.value, argsMap))
                Long::class -> largeUpdate(KSQL.templateHandler.template(update.value, argsMap))
                else -> null
            }
        }

        throw UnsupportedOperationException("没有找到@Query或者@Update")
    }

    private fun prepareStatement(opt: StmtOptions = StmtOptions(), sql: String, autoGeneratedKeys: Int? = null): PStmt {
        val sql = sql.split("\n").filter { it.isNotEmpty() }.joinToString(" ") { it.trim() }
        val stmt = if(autoGeneratedKeys != null){
            conn.prepareStatement(sql, autoGeneratedKeys)
        }else{
            conn.prepareStatement(sql)
        }
        return if(stmt is PStmt){
            stmt
        }else{
            PStmt(opt.populate(stmt), sql).setOnCloseListener{
                if(executeOnce)
                    this@Conn.close()
            }
        }
    }

    override fun prepareStatement(sql: String): PreparedStatement {
        return prepareStatement(StmtOptions(), sql)
    }

    override fun createStatement(): Statement {
        return Stmt(conn.createStatement()).setOnCloseListener{
            if(executeOnce)
                this@Conn.close()
        }
    }

    fun execute(sql: String): Boolean{
        return createStatement().execute(sql)
    }

    override fun setTransactionIsolation(level: Int) {
        conn.transactionIsolation = level
        KSQL.log("set transactionIsolation to {}", level)
    }

    override fun setAutoCommit(autoCommit: Boolean) {
        conn.autoCommit = autoCommit
        KSQL.log("set autoCommit to {}", autoCommit)
    }

    override fun rollback() {
        conn.rollback()
        KSQL.log("connection rollback")
    }

    override fun commit() {
        conn.commit()
        KSQL.log("connection commit")
    }

    override fun close() {
        connThreadLocal.remove()
        conn.close()
        JDBCListener.onReleasedListener?.invoke(this)
        KSQL.log("connection closed")
    }

    companion object{
        private val connThreadLocal = ThreadLocal<Conn>()

        fun current(): Conn? = connThreadLocal.get()

        fun create(connection: Connection): Conn {
            return if(connection is Conn){
                connection
            }else{
                Conn(connection)
            }
        }
    }
}